require "luasql.adodb"

env = assert (luasql.adodb())
print( env )
-- connect to data source
con = assert (env:connect("Provider=SQLOLEDB.1;Password=poethexp;Persist Security Info=True;User ID=sa;Initial Catalog=EOSDB;Data Source=GINGKOBRANK"))
print( con )
-- reset our table
res = con:execute ("DROP TABLE people",0)
print( res )
res = assert (con:execute[[
  CREATE TABLE people(
    name  varchar(50),
    email varchar(50)
  )
]])
-- add a few elements
list = {
  { name="Jose das Couves", email="jose@couves.com", },
  { name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
  { name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
  --res = assert (con:execute(string.format([[
  --  INSERT INTO people
  --  VALUES (?, ?)]], p.name, p.email)
  --))
  res = assert( con:execute( "INSERT INTO people VALUES( ?, ? )", 0, p) )
end
-- retrieve a cursor
cur = assert (con:execute("select * from eos_dict_entry; select * from people;", 0) )
-- print all rows
cols = cur:getcolnames()
table.foreach(  cols, print )
colstype = cur:getcoltypes();
table.foreach(  colstype, print )
numrows = cur:numrows()
print ( "rows: "..numrows)
row = cur:fetch ({}, "a")	-- the rows will be indexed by field names
while row do
  --print( row.name, row.email )
  for k,v in pairs (row) do
  	--print( k, v )
  end
  row = cur:fetch (row, "a")	-- reusing the table of results
end

curnxt = cur:next()
nrow = curnxt:fetch ({}, "a")	-- the rows will be indexed by field names
while nrow do
  --print( row.name, row.email )
  for k,v in pairs (nrow) do
  	print( k, v )
  end
  nrow = curnxt:fetch (nrow, "a")	-- reusing the table of results
end

-- close everything
cur:close()
con:close()
env:close()
